USE [master]
GO
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[VDBA_ReplWarnTranReplLatency]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN DROP PROCEDURE [dbo].[VDBA_ReplWarnTranReplLatency] END
GO

CREATE PROC [dbo].[VDBA_ReplWarnTranReplLatency]
AS

/***********************************************************************************************
| Title:          VDBA_ReplWarnTranReplLatency
| Author:         Nick Ortman 
| Company: 		  Xtivia
| Date Created:   09/09/2013
| Database:       Master
| Executed From:  "VDBA - Replication Warning: Transactional Replication Latency" (Job)
| Description:    Generates alert for Replication.
|	  
*************************************************************************************************/


--Declare Variables
declare @CompanyNm varchar(100),@dt varchar(30),@mail_Id varchar(255)

--Assign values
SET @mail_Id = master.dbo.GetMail()
SET @CompanyNm = master.dbo.GetCompanyNm()
SET @dt = CONVERT(varchar(30),GETDATE(),121)
SET @dt = LEFT(@dt,LEN(@dt) - 4)

-- VDBA Report
--Header information
select 'HEADER INFO' +  '|' + rtrim(ltrim(@dt)) + '|' + rtrim(ltrim(@CompanyNm)) + '|' + @@servername + '|'  + @@servicename + '|' + rtrim(ltrim(@mail_id)) + '|'
union all

--Version information
select 'VERSION|'  +substring(@@version,charindex(' on',@@version) + 4,charindex(' (Build',@@version) - (charindex(' on',@@version) + 4))+'|'+replace(left(@@version,charindex('-',@@version) - 1),'  ',' ') +'|'+cast(serverproperty('productlevel') as varchar(20))+'|'+convert(varchar(5),getdate(),114) 
union all

--Alert
select 'ms_alerts|Replication Warning: Transactional Replication Latency|157|'+ rtrim(ltrim(@dt)) +'|'+ rtrim(ltrim(@mail_id)) + '|'

GO

